package com.anjie.powerproject.repository;

import com.anjie.powerproject.entity.GraphInfo;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.transaction.annotation.Transactional;

import java.util.List;


public interface GraphInfoRepository extends JpaRepository<GraphInfo,Integer> {

    @Transactional
    @Modifying
    @Query(nativeQuery = true,value = "update graph_info set strategy_type = ?1 where graph_id = ?2")
    Integer updateStrategyTypeByGraphId(Integer strategyType,Integer graphId);

    GraphInfo findByGraphId(Integer graphId);

    List<GraphInfo> findAllByUseRule(Integer useRule);

    @Query(nativeQuery = true, value = "SELECT graph_id, graph_end_time, graph_name, graph_start_time, graph_type, strategy_type, use_rule, start_day_num, start_level, start_rels, start_tem, end_day_num, end_level, end_rels, end_tem, start_work_time, end_work_time\n" +
            "FROM graph_info where cast(concat('2020-',graph_start_time) as date) > cast(concat('2020-',?2) as date) and graph_id != ?1\n" +
            "ORDER BY cast(concat('2020-',graph_start_time) as date) - cast(concat('2020-',?2) as date) asc limit 1")
    GraphInfo findAfterGraph(Integer graphId, String endTime);

    @Query(nativeQuery = true, value = "SELECT graph_id, graph_end_time, graph_name, graph_start_time, graph_type, strategy_type, use_rule, start_day_num, start_level, start_rels, start_tem, end_day_num, end_level, end_rels, end_tem, start_work_time, end_work_time\n" +
            "FROM graph_info where cast(concat('2020-',graph_start_time) as date) < cast(concat('2020-',?2) as date) and graph_id != ?1\n" +
            "ORDER BY cast(concat('2020-',?2) as date) - cast(concat('2020-',graph_start_time) as date) desc limit 1")
    GraphInfo findAfterGraph2(Integer graphId, String endTime);


    @Query(nativeQuery = true, value = "SELECT graph_id, graph_end_time, graph_name, graph_start_time, graph_type, strategy_type, use_rule, start_day_num, start_level, start_rels, start_tem, end_day_num, end_level, end_rels, end_tem, start_work_time, end_work_time\n" +
            "FROM graph_info where cast(concat('2021-',graph_end_time) as date) < cast(concat('2021-',?2) as date) and graph_id != ?1\n" +
            "ORDER BY cast(concat('2020-',?2) as date) - cast(concat('2020-',graph_end_time) as date) asc limit 1")
    GraphInfo findBeforeGraph(Integer graphId, String startTime);

    @Query(nativeQuery = true, value = "SELECT graph_id, graph_end_time, graph_name, graph_start_time, graph_type, strategy_type, use_rule, start_day_num, start_level, start_rels, start_tem, end_day_num, end_level, end_rels, end_tem, start_work_time, end_work_time\n" +
            "FROM graph_info where cast(concat('2021-',graph_end_time) as date) > cast(concat('2021-',?2) as date) and graph_id != ?1\n" +
            "ORDER BY cast(concat('2020-',graph_end_time) as date) - cast(concat('2020-',?2) as date) desc limit 1")
    GraphInfo findBeforeGraph2(Integer graphId, String endTime);

    @Query(nativeQuery = true, value = "SELECT * FROM graph_info WHERE \n" +
            "(cast(concat('2020-', graph_start_time) as date) <= cast(concat('2020-', ?1) as date) and " +
            "cast(concat('2020-', graph_end_time) as date) >= cast(concat('2020-', ?1) as date))\n" +
            "OR (cast(concat('2020-', graph_start_time) as date) > cast(concat('2020-', graph_end_time) as date) and " +
            "cast(concat('2020-', graph_start_time) as date) <= cast(concat('2021-', ?1) as date) and " +
            "cast(concat('2021-', graph_end_time) as date) >= cast(concat('2021-', ?1) as date))")
    GraphInfo getGraphByDateStr(String dateStr);
}
